﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using XSTDZ.ERP.Infrastructure.AdoHelper;
using XSTDZ.ERP.Application;
using XSTDZ.ERP.Infrastructure;
using XSTDZ.ERP.Application.DataContracts;
using System.Data;

namespace XSTDZ.ERP.WebApp.Models.Ado
{
    /// <summary>
    /// 提供报表查询
    /// </summary>
    public class ReportQuery
    {
        public ReportQuery() { }
        private string GetOrgIdByOrgCode(string orgCode)
        {
            IOrgService orgServiceImpl = ServiceLocator.Instance.GetService<IOrgService>();
            var org= orgServiceImpl.GetOrg(orgCode);
            return (org ?? new OrgDto()).Id.ToString();
        }
        #region 发货统计
        /// <summary>
        /// 发货统计
        /// </summary>
        /// <returns></returns>
        public string SendProduct(HttpContextBase context)
        {
            var result = SendProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}"; 
        }

        public DataTable SendProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_SendProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"]??"";
            paraValues[2] = context.Request.Form["ProductInfo"]??"";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4]=Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 收退货统计
        /// <summary>
        /// 收退货统计
        /// </summary>
        /// <returns></returns>
        public string TakeReturnProduct(HttpContextBase context)
        {
            var result = TakeReturnProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable TakeReturnProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_TakeReturnProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ProductInfo"] ?? "";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4] = Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 收货统计
        /// <summary>
        /// 收货统计
        /// </summary>
        /// <returns></returns>
        public string TakeProduct(HttpContextBase context)
        {
            var result = TakeProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable TakeProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_TakeProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ProductInfo"] ?? "";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4] = Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 入库统计
        /// <summary>
        /// 入库统计
        /// </summary>
        /// <returns></returns>
        public string InStockProduct(HttpContextBase context)
        {
            var result = InStockProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable InStockProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_InStockProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ProductInfo"] ?? "";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4] = Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 销售统计
        /// <summary>
        /// 销售统计
        /// </summary>
        /// <returns></returns>
        public string SellProduct(HttpContextBase context)
        {
            var result = SellProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable SellProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_SellProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ProductInfo"] ?? "";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4] = Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 销售退货统计
        /// <summary>
        /// 销售退货统计
        /// </summary>
        /// <returns></returns>
        public string SellReturnProduct(HttpContextBase context)
        {
            var result = SellReturnProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable SellReturnProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_SellReturnProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ProductInfo"] ?? "";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4] = Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 退货厂里统计
        /// <summary>
        /// 退货厂里统计
        /// </summary>
        /// <returns></returns>
        public string SendReturnProduct(HttpContextBase context)
        {
            var result = SendReturnProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable SendReturnProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_SendReturnProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ProductInfo"] ?? "";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4] = Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 损溢统计
        /// <summary>
        /// 损溢统计
        /// </summary>
        /// <returns></returns>
        public string ProfitAndLossProduct(HttpContextBase context)
        {
            var result = ProfitAndLossProductData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable ProfitAndLossProductData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_ProfitAndLossProduct_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ProductInfo", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[5];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ProductInfo"] ?? "";
            paraValues[3] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[4] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[4] = Convert.ToDateTime(paraValues[4]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 销售收款情况（明细）
        /// <summary>
        /// 销售收款情况（明细）
        /// </summary>
        /// <returns></returns>
        public string ReceivablePayableSell(HttpContextBase context)
        {
            var result = ReceivablePayableSellData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable ReceivablePayableSellData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_ReceivablePayableSell_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ClientInfo","@QueryState", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[6];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ClientInfo"] ?? "";
            paraValues[3] = context.Request.Form["QueryState"] ?? "";
            paraValues[4] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[5] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[5] = Convert.ToDateTime(paraValues[5]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 销售退款情况（明细）
        /// <summary>
        /// 销售退款情况（明细）
        /// </summary>
        /// <returns></returns>
        public string ReceivablePayableSellReturn(HttpContextBase context)
        {
            var result = ReceivablePayableSellReturnData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable ReceivablePayableSellReturnData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_ReceivablePayableSellReturn_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ClientInfo", "@QueryState", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[6];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ClientInfo"] ?? "";
            paraValues[3] = context.Request.Form["QueryState"] ?? "";
            paraValues[4] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[5] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[5] = Convert.ToDateTime(paraValues[5]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 调拨收款情况（明细）
        /// <summary>
        /// 调拨收款情况（明细）
        /// </summary>
        /// <returns></returns>
        public string ReceivablePayableAllotShou(HttpContextBase context)
        {
            var result = ReceivablePayableAllotShouData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable ReceivablePayableAllotShouData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_ReceivablePayableAllotShou_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ClientInfo", "@QueryState", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[6];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ClientInfo"] ?? "";
            paraValues[3] = context.Request.Form["QueryState"] ?? "";
            paraValues[4] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[5] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[5] = Convert.ToDateTime(paraValues[5]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
        #region 调拨付款情况（明细）
        /// <summary>
        /// 调拨付款情况（明细）
        /// </summary>
        /// <returns></returns>
        public string ReceivablePayableAllotFu(HttpContextBase context)
        {
            var result = ReceivablePayableAllotFuData(context);//获取数据步骤
            //5、转化结果为JSON格式
            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            //6、返回结果
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count.ToString() + "\"}";
        }

        public DataTable ReceivablePayableAllotFuData(HttpContextBase context)
        {
            //1、存储过程名
            string procName = @"[dbo].[Rpt_ReceivablePayableAllotFu_Total]";
            //2、存储过程参数名集合
            string[] paraKeys = { "@OrgID", "@NumberNo", "@ClientInfo", "@QueryState", "@StartTime", "@EndTime" };
            //3、存储过程参数值集合
            string[] paraValues = new string[6];
            #region 参数值获取赋值
            string orgCode = context.Request["orgCode"];
            paraValues[0] = GetOrgIdByOrgCode(orgCode); //string.Format(@" and a.OrgID  in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            paraValues[1] = context.Request.Form["NumberNo"] ?? "";
            paraValues[2] = context.Request.Form["ClientInfo"] ?? "";
            paraValues[3] = context.Request.Form["QueryState"] ?? "";
            paraValues[4] = context.Request.Form["StartTime"] ?? (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)).ToString("yyyy-MM-dd");
            paraValues[5] = context.Request.Form["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            paraValues[5] = Convert.ToDateTime(paraValues[5]).AddDays(1).ToString("yyyy-MM-dd");
            #endregion
            //4、执行存储过程 返回DataTable
            return SqlHelperEnhance.ExecuteReaderProc(procName, paraKeys, paraValues);
        }
        #endregion
    }
}